﻿Public Class FrmProspectsMAJ
    Public pCD_CLI As Integer

    Private szSQL As String
    Private rs As ADODB.Recordset


    Private Sub FrmProspectsMAJ_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        InitComboPermis(CB_PERMIS)
        InitcomboAbandons()
        InitcomboOrigines()
        InitcomboTypesContact()
        InitcomboTypesFormation()
        TC_DETAILS.SelectedTab = TabPage1

        ' utilisateur autorisé à modifier ?
        If gUserAcces < 6 Then
            BO_ENREGISTRER.Enabled = False
            BO_SUPPRIMER.Enabled = False
            BO_ARELANCER.Enabled = False
            BO_RELANCE.Enabled = False
            BO_GAGNE.Enabled = False
            BO_PERDU.Enabled = False
        Else
            BO_ENREGISTRER.Enabled = True
            BO_SUPPRIMER.Enabled = True
            BO_ARELANCER.Enabled = True
            BO_RELANCE.Enabled = True
            BO_GAGNE.Enabled = True
            BO_PERDU.Enabled = True
        End If
        ProspectToScreen()

    End Sub

    Private Sub ProspectToScreen()
        ' création de fiche
        If pCD_CLI = 0 Then
            ClearTextBoxes(Me)
            CB_QUALITE.SelectedIndex = 0
            CB_TYPES.SelectedIndex = 0
            CB_POURQUI.SelectedIndex = -1
            CB_PERMIS.SelectedIndex = CB_PERMIS.FindString("NR")
            CB_PERMIS_OBTENU.SelectedIndex = -1
            CB_MARIETTON.SelectedIndex = -1
            CB_ORIGINES.SelectedIndex = -1
            CB_INSCRIT_AUTRE_AE.SelectedIndex = -1
            CB_TYPE_FORMATION.SelectedIndex = CB_TYPE_FORMATION.FindString("NR")
            CB_ECHEANCE_FORMATION.SelectedIndex = -1
            Exit Sub
        End If

        ' Lire la fiche Prospect
        szSQL = "CALL spGet_PROSPECTS_byCODE(" & pCD_CLI & ")"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount > 0 Then
            CB_TYPES.SelectedIndex = CB_TYPES.FindString(rs.Fields("type_contact").Value.ToString)
            CB_POURQUI.SelectedIndex = CB_POURQUI.FindString(rs.Fields("pour_qui").Value.ToString)
            TE_AGE.Text = nv(rs.Fields("age").Value)
            CB_PERMIS.SelectedIndex = CB_PERMIS.FindString(rs.Fields("permis_demande").Value.ToString)
            CB_PERMIS_OBTENU.SelectedIndex = CB_PERMIS_OBTENU.FindString(rs.Fields("permis_obtenu").Value.ToString)
            CB_MARIETTON.SelectedIndex = CB_MARIETTON.FindString(rs.Fields("client_marietton").Value.ToString)
            CB_ORIGINES.SelectedIndex = CB_ORIGINES.FindString(rs.Fields("origine").Value.ToString)
            TE_ORIGINE_COMPL.Text = nv(rs.Fields("origine_compl").Value)
            CB_INSCRIT_AUTRE_AE.SelectedIndex = CB_INSCRIT_AUTRE_AE.FindString(rs.Fields("inscrit_autre_ae").Value.ToString)
            TE_EFFECTUE_AUTRE_AE.Text = nv(rs.Fields("effectue_autre_ae").Value)
            CB_TYPE_FORMATION.SelectedIndex = CB_TYPE_FORMATION.FindString(rs.Fields("type_formation").Value.ToString)
            CB_ECHEANCE_FORMATION.SelectedIndex = CB_ECHEANCE_FORMATION.FindString(rs.Fields("echeance_formation").Value.ToString)
            TE_DISPONIBILITES.Text = nv(rs.Fields("disponibilites").Value)
            TE_OBSERVATION.Text = nv(rs.Fields("observation").Value)

            TE_A_RELANCER_LE.Text = nv(rs.Fields("a_relancer_le").Value)
            TE_PROCHAIN_RDV_LE.Text = IfDatetime(rs.Fields("prochain_rdv_le").Value)
            TE_RELANCE1_LE.Text = nv(rs.Fields("date_relance1").Value)
            TE_RELANCE2_LE.Text = nv(rs.Fields("date_relance2").Value)
            TE_RELANCE3_LE.Text = nv(rs.Fields("date_relance3").Value)
            TE_RESULTAT1.Text = nv(rs.Fields("resultat1").Value)
            TE_RESULTAT2.Text = nv(rs.Fields("resultat2").Value)
            TE_RESULTAT3.Text = nv(rs.Fields("resultat3").Value)
            TE_CLOTURE_LE.Text = IfDate(rs.Fields("cloture_le").Value)
            TE_CD_CLI.Text = rs.Fields("cd_cli").Value
            TE_STATUT.Text = nv(rs.Fields("statut").Value)
            If rs.Fields("statut").Value = "Gagné" Then
                TE_STATUT.ForeColor = Color.Navy
            ElseIf rs.Fields("statut").Value = "Perdu" Then
                TE_STATUT.ForeColor = Color.Gray
            End If
            TE_MOTIF.Text = rs.Fields("motif_abandon").Value.ToString

            TE_CD_PROSPECT.Text = Format(rs.Fields("cd_prospect").Value, "000000")
            TE_MODIFIE_LE.Text = setModifiedBy(rs.Fields("cree_le").Value, rs.Fields("modif_le").Value, rs.Fields("cd_uti").Value)
            CB_QUALITE.SelectedIndex = CB_QUALITE.FindString(rs.Fields("civilite").Value.ToString)
            TE_NOM.Text = rs.Fields("nom").Value
            TE_PRENOM.Text = rs.Fields("prenom").Value
            TE_VILLE.Text = nv(rs.Fields("ville").Value)
            TE_TEL1.Text = nv(rs.Fields("tel_fixe").Value)
            TE_TEL2.Text = nv(rs.Fields("tel_portable").Value)
            TE_TEL3.Text = nv(rs.Fields("tel_pere").Value)
            TE_TEL4.Text = nv(rs.Fields("tel_mere").Value)
            TE_EMAIL.Text = nv(rs.Fields("email").Value)
            TE_TOTAL_RDV.Text = rs.Fields("total_rdv").Value
        End If
        rs.Close()

        Me.Text = "Fiche Prospect " & TE_NOM.Text
        ' utilisateur autorisé à modifier ?
        If TE_CLOTURE_LE.TextLength > 0 Then
            BO_ENREGISTRER.Enabled = False
            BO_SUPPRIMER.Enabled = False
            BO_ARELANCER.Enabled = False
            BO_RELANCE.Enabled = False
            BO_GAGNE.Enabled = False
            BO_PERDU.Enabled = False
        End If
    End Sub

    ' Contrôles de saisie de la fiche PROSPECTS
    Private Function IsSaisie_OK(bRelance As Boolean) As Boolean

        IsSaisie_OK = False

        ' Controle des zones obligatoires
        If TE_NOM.TextLength = 0 Or TE_PRENOM.TextLength = 0 Then
            MsgBox("Le nom et le prénom sont obligatoires", MsgBoxStyle.Critical)
            TE_NOM.SelectAll()
            TE_NOM.Focus()
            Exit Function
        End If
        If TE_VILLE.TextLength = 0 Then
            MsgBox("La saisie de la ville est obligatoire", MsgBoxStyle.Critical)
            TE_NOM.SelectAll()
            TE_NOM.Focus()
            Exit Function
        End If
        ' si email saisi, est-il valide ?
        If TE_EMAIL.TextLength > 0 And IsEmailValid(TE_EMAIL.Text) = False Then
            MsgBox("L'adresse email est incorrecte", MsgBoxStyle.Critical)
            TE_EMAIL.Focus()
            Exit Function
        End If
        If TE_A_RELANCER_LE.TextLength > 0 Then
            If IsDate(TE_A_RELANCER_LE.Text) = False Then
                MsgBox("Format de date incorrect", MsgBoxStyle.Critical)
                TE_A_RELANCER_LE.SelectAll()
                TE_A_RELANCER_LE.Focus()
                Exit Function
            End If
        End If
        If TE_RELANCE1_LE.TextLength > 0 Then
            If IsDate(TE_RELANCE1_LE.Text) = False Then
                MsgBox("Format de date incorrect", MsgBoxStyle.Critical)
                TE_RELANCE1_LE.SelectAll()
                TE_RELANCE1_LE.Focus()
                Exit Function
            Else
                TE_RELANCE1_LE.Text = CDate(TE_RELANCE1_LE.Text)
            End If
            If TE_RESULTAT1.TextLength = 0 Then
                MsgBox("Veuillez renseigner le resultat", MsgBoxStyle.Critical)
                TE_RESULTAT1.Focus()
                Exit Function
            End If
        End If
        If TE_RELANCE2_LE.TextLength > 0 Then
            If IsDate(TE_RELANCE2_LE.Text) = False Then
                MsgBox("Format de date incorrect", MsgBoxStyle.Critical)
                TE_RELANCE2_LE.SelectAll()
                TE_RELANCE2_LE.Focus()
                Exit Function
            Else
                TE_RELANCE2_LE.Text = CDate(TE_RELANCE2_LE.Text)
            End If
            If TE_RESULTAT2.TextLength = 0 Then
                MsgBox("Veuillez renseigner aussi le resultat", MsgBoxStyle.Critical)
                TE_RELANCE2_LE.Focus()
                Exit Function
            End If
        End If
        If TE_RELANCE3_LE.TextLength > 0 Then
            If IsDate(TE_RELANCE3_LE.Text) = False Then
                MsgBox("Format de date incorrect", MsgBoxStyle.Critical)
                TE_RELANCE3_LE.SelectAll()
                TE_RELANCE3_LE.Focus()
                Exit Function
            Else
                TE_RELANCE3_LE.Text = CDate(TE_RELANCE3_LE.Text)
            End If
            If TE_RESULTAT3.TextLength = 0 Then
                MsgBox("Veuillez renseigner aussi le resultat", MsgBoxStyle.Critical)
                TE_RESULTAT3.Focus()
                Exit Function
            End If
        End If
        If TE_A_RELANCER_LE.TextLength = 0 And TE_PROCHAIN_RDV_LE.TextLength = 0 And TE_CLOTURE_LE.TextLength = 0 Then
            If bRelance = True Then
                TE_A_RELANCER_LE.Text = Now.ToShortDateString
            Else
                MsgBox("Au moins une date suivante doit être renseignée :" & vbCrLf & "A relancer le, Prochain rdv, Clôturé le", MsgBoxStyle.Critical)
                TE_A_RELANCER_LE.SelectAll()
                TE_A_RELANCER_LE.Focus()
                Exit Function
            End If
        End If

        IsSaisie_OK = True

    End Function

    
    Private Sub BO_ENREGISTRER_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BO_ENREGISTRER.Click

        If IsSaisie_OK(False) = True Then
            Call ScreenToProspect()
            ' fermer la fenetre
            Me.Close()
        End If

    End Sub

    Private Sub InitcomboTypesContact()

        ' vider le combo
        CB_TYPES.Items.Clear()
        ' Lire la table CATEGORIES
        szSQL = "CALL spGet_P_TABLES_byCODE('p_types_contact',0)"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            CB_TYPES.Items.Add(rs.Fields("libelle").Value)
            rs.MoveNext()
        Loop
        ' Close the recordset AND connection.
        rs.Close()

    End Sub

    Private Sub InitcomboTypesFormation()

        ' vider le combo
        CB_TYPE_FORMATION.Items.Clear()
        ' Lire la table CATEGORIES
        szSQL = "CALL spGet_P_TABLES_byCODE('p_types_formation',0)"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            CB_TYPE_FORMATION.Items.Add(rs.Fields("libelle").Value)
            rs.MoveNext()
        Loop
        ' Close the recordset AND connection.
        rs.Close()

    End Sub

    Private Sub InitcomboOrigines()

        ' vider le combo
        CB_ORIGINES.Items.Clear()
        ' Lire la table CATEGORIES
        szSQL = "CALL spGet_P_TABLES_byCODE('p_origines',0)"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            CB_ORIGINES.Items.Add(rs.Fields("libelle").Value)
            rs.MoveNext()
        Loop
        ' Close the recordset AND connection.
        rs.Close()

    End Sub

    Private Sub InitcomboAbandons()

        ' vider le combo
        CB_ABANDONS.Items.Clear()
        ' Lire la table CATEGORIES
        szSQL = "CALL spGet_P_TABLES_byCODE('p_abandons',0)"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            CB_ABANDONS.Items.Add(rs.Fields("libelle").Value)
            rs.MoveNext()
        Loop
        ' Close the recordset AND connection.
        rs.Close()

    End Sub

    Private Sub BO_SUPPRIMER_Click(sender As System.Object, e As System.EventArgs) Handles BO_SUPPRIMER.Click
        ' utilisateur autorisé à supprimer ?
        If IsUserAuthorised(9) = False Then
            Exit Sub
        End If
        ' suppression
        deleteEnregistrement("PROSPECTS", TE_NOM.Text, TE_CD_PROSPECT.Text)
        ' fermer la fenêtre
        Me.Close()
    End Sub

    Private Sub InitListeRDV(ByVal ix As Integer)

        Dim nb As Integer = 0

        ' effacer la listview
        LV_INSCRITS.Items.Clear()
        ' Lire les suivis de l'élève
        szSQL = "CALL spGET_PROSPECTS_RDV_byPROSPECT(" & pCD_CLI & ",0)"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            LV_INSCRITS.Items.Add(rs.Fields("no_ligne").Value)
            LV_INSCRITS.Items(nb).SubItems.Add(Format(rs.Fields("debut_rdv").Value, "dd/MM/yyyy"))
            LV_INSCRITS.Items(nb).SubItems.Add(Format(rs.Fields("debut_rdv").Value, "HH:mm"))
            LV_INSCRITS.Items(nb).SubItems.Add(nv(rs.Fields("objet_rdv").Value))
            LV_INSCRITS.Items(nb).SubItems.Add(nv(rs.Fields("contact").Value))
            LV_INSCRITS.Items(nb).SubItems.Add(rs.Fields("statut").Value)
            LV_INSCRITS.Items(nb).SubItems.Add(nv(rs.Fields("resultat").Value))
            rs.MoveNext()
            nb = nb + 1
        Loop
        ' Close the recordset AND connection.
        rs.Close()
        setIndice(0, LV_INSCRITS)

    End Sub

    Private Sub BO_GAGNE_Click(sender As System.Object, e As System.EventArgs) Handles BO_GAGNE.Click
        ' controler que la date du prochain rdv est vide 
        If TE_PROCHAIN_RDV_LE.TextLength > 0 Then
            MsgBox("Le prospect a un rendez-vous programmé, clôture impossible.", MsgBoxStyle.Critical)
            TE_A_RELANCER_LE.Focus()
            Exit Sub
        End If

        If MsgBox("Ce traitement va clôturer cette fiche et " & vbCrLf & _
                          "la convertir en client." & vbCrLf & _
                  "Etes-vous sûr de vouloir convertir ce prospect ?", _
                  MsgBoxStyle.YesNo, "Prospect gangné") = MsgBoxResult.No Then
            Exit Sub
        End If

        ' enregistrer la fiche en cours
        Call ScreenToProspect()
        ' conversion du prospect en client
        szSQL = "CALL spUPD_PROSPECTS_GAGNE(" & pCD_CLI & ",'" & gUserCode & "'," & gUserAgence & ")"
        ExecQuery(ctDSN_AEM, szSQL)

        ' quitter le formulaire
        Me.Close()

    End Sub

    Private Sub BO_AGENDA_Click(sender As System.Object, e As System.EventArgs) Handles BO_AGENDA.Click
        ' enregistrer la fiche en cours
        If IsSaisie_OK(True) = True Then
            Call ScreenToProspect()
            ' ouvre AGENDA
            frmAgenda.ShowDialog()
        End If

    End Sub

    Private Sub BO_PERDU_Click(sender As System.Object, e As System.EventArgs) Handles BO_PERDU.Click
        ' controler que la date du prochain rdv est vide 
        If TE_PROCHAIN_RDV_LE.TextLength > 0 Then
            MsgBox("Le prospect a un rendez-vous programmé, clôture impossible.", MsgBoxStyle.Critical)
            TE_A_RELANCER_LE.Focus()
            Exit Sub
        End If

        ' enregistrer la fiche en cours
        Call ScreenToProspect()
        ' affiche la TabPage ABANDON pour saisie motif ABANDON
        CB_ABANDONS.SelectedIndex = 0
        TC_DETAILS.SelectedTab = TabPage3

    End Sub

    Private Sub TC_DETAILS_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles TC_DETAILS.SelectedIndexChanged
        If TC_DETAILS.SelectedIndex = 2 Then
            TC_DETAILS.SelectedTab = TabPage1
        End If
        If TC_DETAILS.SelectedIndex = 1 Then
            ' afficher sa liste de RDV
            InitListeRDV(0)
        End If

    End Sub

    Private Sub BO_VALIDER_Click(sender As System.Object, e As System.EventArgs) Handles BO_VALIDER.Click

        If MsgBox("Ce traitement va clôturer cette fiche. " & vbCrLf & _
                  "Le traitement est irrévsersible." & vbCrLf & _
                   "Etes-vous sûr de vouloir clôturé ce prospect ?", _
                   MsgBoxStyle.YesNo + MsgBoxStyle.Question, "Prospect gangné") = MsgBoxResult.No Then
            Exit Sub
        End If

        ' conversion du prospect en perdu
        szSQL = "CALL spUPD_PROSPECTS_PERDU(" & pCD_CLI & ",'" & CB_ABANDONS.SelectedItem & "','" & gUserCode & "')"
        ExecQuery(ctDSN_AEM, szSQL)

        ' quitter le formulaire
        Me.Close()


    End Sub

    Private Sub BO_ARELANCER_Click(sender As System.Object, e As System.EventArgs) Handles BO_ARELANCER.Click

        ' enregistrer la fiche en cours
        Call ScreenToProspect()
        ' mettre la date A relncer à J + 7
        szSQL = "CALL spUPD_PROSPECTS_ARELANCER(" & pCD_CLI & ",7)"
        ExecQuery(ctDSN_AEM, szSQL)

        ' réaffiche la fiche
        ProspectToScreen()

    End Sub

    Private Sub BO_RELANCE_Click(sender As System.Object, e As System.EventArgs) Handles BO_RELANCE.Click
        ' enregistrer la fiche en cours
        If IsSaisie_OK(True) = True Then
            Call ScreenToProspect()
            ' ouvre AGENDA
            frmAgenda.ShowDialog()
        End If

    End Sub

    Private Sub ScreenToProspect()

        ' mise à jour PROSPECTS
        szSQL = "CALL spUpd_PROSPECTS(" & pCD_CLI & ",'" & gUserCode & "','" & CB_QUALITE.SelectedItem & "','" & nq(TE_NOM.Text) & "','" & nq(TE_PRENOM.Text) & "','" & _
            nq(TE_VILLE.Text) & "','" & TE_TEL1.Text & "','" & TE_TEL2.Text & "','" & TE_TEL3.Text & "','" & TE_TEL4.Text & "','" & TE_EMAIL.Text & "','" & _
            CB_TYPES.SelectedItem & "','" & CB_POURQUI.SelectedItem & "','" & CB_PERMIS.SelectedItem & "','" & TE_AGE.Text & "','" & _
            CB_PERMIS_OBTENU.SelectedItem & "','" & CB_MARIETTON.SelectedItem & "','" & CB_ORIGINES.SelectedItem & "','" & TE_ORIGINE_COMPL.Text & "','" & _
            CB_INSCRIT_AUTRE_AE.SelectedItem & "','" & TE_EFFECTUE_AUTRE_AE.Text & "','" & CB_TYPE_FORMATION.SelectedItem & "','" & _
            CB_ECHEANCE_FORMATION.SelectedItem & "','" & TE_DISPONIBILITES.Text & "','" & nq(TE_OBSERVATION.Text) & "','" & TE_A_RELANCER_LE.Text & "','" & _
            TE_RELANCE1_LE.Text & "','" & TE_RELANCE2_LE.Text & "','" & TE_RELANCE3_LE.Text & "','" & _
            TE_RESULTAT1.Text & "','" & TE_RESULTAT2.Text & "','" & TE_RESULTAT3.Text & "'," & gUserAgence & ")"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount > 0 Then
            pCD_CLI = rs.Fields("newcode").Value
        End If

    End Sub

    Private Sub LV_INSCRITS_DoubleClick(sender As Object, e As System.EventArgs) Handles LV_INSCRITS.DoubleClick
        If LV_INSCRITS.SelectedItems.Count > 0 Then
            ' afficher les détails de la ligne NoLigne
            frmAgendaLigne.pNoLigne = LV_INSCRITS.SelectedItems(0).Text
            frmAgendaLigne.pDateTime = Now()
            frmAgendaLigne.ShowDialog()
            InitListeRDV(LV_INSCRITS.SelectedItems(0).Index)
        End If

    End Sub

    Private Sub LV_INSCRITS_KeyDown(sender As Object, e As System.Windows.Forms.KeyEventArgs) Handles LV_INSCRITS.KeyDown
        If e.KeyCode = Keys.Enter Then
            LV_INSCRITS_DoubleClick(sender, e)
        End If

    End Sub

End Class